#!/usr/bin/env node

const { execSync, spawn } = require('child_process');
const fs = require('fs');
const path = require('path');
const chalk = require('chalk');
const dotenv = require('dotenv');

class DatabaseInstaller {
    constructor() {
        this.workDir = __dirname;
        this.sqlPath = path.join(this.workDir, 'table');
        this.logFile = path.join(this.workDir, `exec_${this.getTimestamp()}.log`);

        // 加载环境变量
        this.loadEnvConfig();

        this.config = {
            db_host: process.env.DB_HOST || 'localhost',
            db_port: parseInt(process.env.DB_PORT) || 3306,
            db_name: process.env.DB_NAME || 'okx_robot_db',
            db_user: process.env.DB_USERNAME || 'root',
            db_pass: process.env.DB_PASSWORD || 'admin123*'
        };
    }

    loadEnvConfig() {
        // 尝试从项目根目录加载环境配置文件
        const projectRoot = path.resolve(this.workDir, '../../');
        const baseEnvPath = path.join(projectRoot, '.env');
        const envFilePath = path.join(projectRoot, `.env.${process.env.NODE_ENV || 'development'}`);

        // 首先加载基础 .env 文件
        if (fs.existsSync(baseEnvPath)) {
            dotenv.config({ path: baseEnvPath });
            this.log('✅ 从 .env 文件加载基础配置', 'success');
        }

        // 然后加载环境特定的配置文件
        if (fs.existsSync(envFilePath)) {
            dotenv.config({ path: envFilePath, override: true });
            this.log(`✅ 从 .env.${process.env.NODE_ENV || 'development'} 文件加载环境配置`, 'success');
        } else {
            this.log(`⚠️  未找到 .env.${process.env.NODE_ENV || 'development'} 文件，使用默认配置`, 'warning');
            this.log('💡 建议创建环境配置文件来配置数据库连接', 'info');
        }
    }

    getTimestamp() {
        const now = new Date();
        return now.toISOString().replace(/[:.]/g, '-').slice(0, -5);
    }

    log(message, type = 'info') {
        const timestamp = new Date().toISOString();
        const logMessage = `[${timestamp}] ${message}\n`;

        // 写入日志文件
        fs.appendFileSync(this.logFile, logMessage);

        // 控制台输出
        switch (type) {
            case 'success':
                console.log(chalk.green(message));
                break;
            case 'error':
                console.log(chalk.red(message));
                break;
            case 'warning':
                console.log(chalk.yellow(message));
                break;
            case 'info':
            default:
                console.log(chalk.blue(message));
                break;
        }
    }

    async checkMySQL() {
        try {
            execSync('mysql --version', { stdio: 'pipe' });
            this.log('✅ MySQL 客户端检查通过', 'success');
            return true;
        } catch (error) {
            this.log('❌ MySQL 客户端未找到，请确保 MySQL 已安装并添加到 PATH', 'error');
            this.log('💡 请安装 MySQL 或确保 mysql 命令可用', 'warning');
            return false;
        }
    }

    async testConnection() {
        try {
            const command = `mysql -h ${this.config.db_host} -u${this.config.db_user} -p${this.config.db_pass} -P${this.config.db_port} -e "SELECT 1;"`;
            execSync(command, { stdio: 'pipe' });
            this.log('✅ 数据库连接测试成功', 'success');
            return true;
        } catch (error) {
            this.log('❌ 数据库连接失败', 'error');
            this.log('请检查以下配置:', 'warning');
            this.log(`  主机: ${this.config.db_host}`, 'info');
            this.log(`  端口: ${this.config.db_port}`, 'info');
            this.log(`  用户: ${this.config.db_user}`, 'info');
            this.log(`  密码: ${this.config.db_pass}`, 'info');
            return false;
        }
    }

    async createDatabase() {
        try {
            this.log('🗄️  正在创建数据库...', 'info');

            const schemaFile = path.join(this.workDir, 'db_schema.sql');

            // 读取 SQL 文件内容并替换数据库名称
            let sqlContent = fs.readFileSync(schemaFile, 'utf8');
            sqlContent = sqlContent.replace(/\{\{DB_NAME\}\}/g, this.config.db_name);

            // 创建临时 SQL 文件
            const tempSchemaPath = path.join(this.workDir, 'temp_db_schema.sql');
            fs.writeFileSync(tempSchemaPath, sqlContent);

            const command = `mysql -h ${this.config.db_host} -u${this.config.db_user} -p${this.config.db_pass} -P${this.config.db_port} --default-character-set=utf8mb4 < "${tempSchemaPath}"`;

            try {
                execSync(command, { stdio: 'pipe' });
                this.log('✅ 数据库创建成功', 'success');
            } finally {
                // 清理临时文件
                if (fs.existsSync(tempSchemaPath)) {
                    fs.unlinkSync(tempSchemaPath);
                }
            }

            return true;
        } catch (error) {
            this.log('❌ 数据库创建失败', 'error');
            this.log(error.message, 'error');
            return false;
        }
    }

        async createTables() {
        try {
            this.log('📊 正在创建数据表...', 'info');

            const files = fs.readdirSync(this.sqlPath).filter(file => file.endsWith('.sql') && !file.startsWith('temp_'));

            for (const file of files) {
                const filePath = path.join(this.sqlPath, file);
                this.log(`📝 处理文件: ${file}`, 'info');

                // 读取 SQL 文件内容
                let sqlContent = fs.readFileSync(filePath, 'utf8');

                // 动态替换管理员账户信息
                sqlContent = this.replaceAdminPlaceholders(sqlContent);

                // 动态替换系统配置信息
                sqlContent = this.replaceSystemConfigPlaceholders(sqlContent);

                // 创建临时 SQL 文件
                const tempSqlPath = path.join(this.sqlPath, `temp_${file}`);
                fs.writeFileSync(tempSqlPath, sqlContent);

                // 调试：显示临时文件路径
                this.log(`🔍 临时文件路径: ${tempSqlPath}`, 'info');

                const command = `mysql -h ${this.config.db_host} -u${this.config.db_user} -p${this.config.db_pass} -P${this.config.db_port} --default-character-set=utf8mb4 ${this.config.db_name} < "${tempSqlPath}"`;

                try {
                    execSync(command, { stdio: 'pipe' });
                    this.log(`✅ 文件 ${file} 执行成功`, 'success');
                } catch (error) {
                    this.log(`❌ 文件 ${file} 执行失败`, 'error');
                    this.log(error.message, 'error');
                    this.log(`🔍 临时文件内容预览:`, 'info');
                    this.log(fs.readFileSync(tempSqlPath, 'utf8').substring(0, 500) + '...', 'info');
                    this.log(`🔍 临时文件保留在: ${tempSqlPath}`, 'info');
                    // 保留临时文件以便调试
                    return false;
                }

                // 清理临时文件
                if (fs.existsSync(tempSqlPath)) {
                    fs.unlinkSync(tempSqlPath);
                }
            }

            return true;
        } catch (error) {
            this.log('❌ 创建数据表失败', 'error');
            this.log(error.message, 'error');
            return false;
        }
    }

    replaceAdminPlaceholders(sqlContent) {
        const bcrypt = require('bcryptjs');

        // 从环境变量获取管理员配置
        const adminConfig = {
            username: process.env.ADMIN_USERNAME || 'admin',
            email: process.env.ADMIN_EMAIL || 'admin@okx-robot.com',
            password: process.env.ADMIN_PASSWORD || 'admin123',
            role: process.env.ADMIN_ROLE || 'admin'
        };

        // 生成密码哈希
        const passwordHash = bcrypt.hashSync(adminConfig.password, 10);

        // 替换管理员账户占位符
        const adminInsertSql = `
-- ----------------------------
-- 初始化超级管理员账户
-- ----------------------------
INSERT INTO \`users\` (
  \`id\`,
  \`username\`,
  \`email\`,
  \`password_hash\`,
  \`role\`,
  \`status\`,
  \`created_at\`,
  \`updated_at\`
) VALUES (
  1,
  '${adminConfig.username}',
  '${adminConfig.email}',
  '${passwordHash}',
  '${adminConfig.role}',
  'active',
  NOW(),
  NOW()
) ON DUPLICATE KEY UPDATE
  \`updated_at\` = NOW(),
  \`updated_by\` = 1;`;

        // 替换占位符
        return sqlContent.replace(
            /-- 此部分将由安装脚本动态生成，不在此处硬编码/,
            adminInsertSql
        );
    }

    replaceSystemConfigPlaceholders(sqlContent) {
        // 从环境变量获取系统配置
        const systemConfigs = [
            { key: 'system_name', value: process.env.APP_NAME || 'OKX Trading Robot', description: '系统名称' },
            { key: 'system_version', value: process.env.APP_VERSION || '1.0.0', description: '系统版本' },
            { key: 'maintenance_mode', value: process.env.MAINTENANCE_MODE || 'false', description: '维护模式' },
            { key: 'max_login_attempts', value: process.env.MAX_LOGIN_ATTEMPTS || '5', description: '最大登录尝试次数' },
            { key: 'session_timeout', value: process.env.SESSION_TIMEOUT || '3600', description: '会话超时时间(秒)' }
        ];

        // 生成系统配置插入 SQL - 使用 NULL 值避免外键约束问题
        const configValues = systemConfigs.map((config, index) =>
            `(${index + 1}, '${config.key}', '${config.value}', '${config.description}', 1, NULL, NOW(), NOW(), NULL, NULL)`
        ).join(',\n');

        const systemConfigSql = `
-- ----------------------------
-- 初始化系统配置数据
-- ----------------------------
INSERT INTO \`system_configs\` (
  \`id\`,
  \`config_key\`,
  \`config_value\`,
  \`description\`,
  \`is_system\`,
  \`deleted_at\`,
  \`created_at\`,
  \`updated_at\`,
  \`created_by\`,
  \`updated_by\`
) VALUES
${configValues}
ON DUPLICATE KEY UPDATE
  \`config_value\` = VALUES(\`config_value\`),
  \`updated_at\` = NOW(),
  \`updated_by\` = NULL;`;

        // 替换占位符
        return sqlContent.replace(
            /-- 此部分将由安装脚本动态生成，不在此处硬编码/,
            systemConfigSql
        );
    }

    showCompletionMessage() {
        // 从环境变量获取管理员配置
        const adminConfig = {
            username: process.env.ADMIN_USERNAME || 'admin',
            email: process.env.ADMIN_EMAIL || 'admin@okx-robot.com',
            password: process.env.ADMIN_PASSWORD || 'admin123',
            role: process.env.ADMIN_ROLE || 'admin'
        };

        // 从环境变量获取系统配置
        const systemConfig = {
            name: process.env.APP_NAME || 'OKX Trading Robot',
            version: process.env.APP_VERSION || '1.0.0',
            maintenance: process.env.MAINTENANCE_MODE || 'false'
        };

        console.log('\n' + '='.repeat(50));
        console.log(chalk.green('🎉 初始化完成！'));
        console.log('='.repeat(50));
        console.log();
        console.log(chalk.cyan('📋 超级管理员账户信息:'));
        console.log(`   用户名: ${adminConfig.username}`);
        console.log(`   邮箱: ${adminConfig.email}`);
        console.log(`   密码: ${adminConfig.password}`);
        console.log(`   角色: ${adminConfig.role}`);
        console.log();
        console.log(chalk.yellow('⚠️  重要提醒:'));
        console.log('   1. 请立即修改默认密码！');
        console.log('   2. 在生产环境中使用强密码');
        console.log('   3. 定期更换密码');
        console.log();
        console.log(chalk.blue('🔗 系统配置:'));
        console.log(`   - 系统名称: ${systemConfig.name}`);
        console.log(`   - 系统版本: ${systemConfig.version}`);
        console.log(`   - 维护模式: ${systemConfig.maintenance === 'true' ? '开启' : '关闭'}`);
        console.log();
        console.log('='.repeat(50));
        console.log();
        console.log(chalk.gray('📊 数据库信息:'));
        console.log(`   数据库名称: ${this.config.db_name}`);
        console.log(`   数据库主机: ${this.config.db_host}:${this.config.db_port}`);
        console.log(`   执行日志: ${this.logFile}`);
        console.log();
        console.log(chalk.gray('🔧 配置来源:'));
        console.log(`   环境变量: ${process.env.NODE_ENV || 'development'}`);
        const envFile = path.join(this.workDir, `../../.env.${process.env.NODE_ENV || 'development'}`);
        console.log(`   配置文件: ${fs.existsSync(envFile) ? '已加载' : '未找到'} (${path.basename(envFile)})`);
        console.log();
    }

    async run() {
        try {
            console.log(chalk.blue('🤖 OKX Trading Robot - 数据库安装工具'));
            console.log(chalk.gray('='.repeat(50)));
            console.log();

            // 检查 MySQL 客户端
            if (!await this.checkMySQL()) {
                process.exit(1);
            }

            // 测试数据库连接
            if (!await this.testConnection()) {
                process.exit(1);
            }

            // 创建数据库
            if (!await this.createDatabase()) {
                process.exit(1);
            }

            // 创建数据表
            if (!await this.createTables()) {
                process.exit(1);
            }

            // 显示完成信息
            this.showCompletionMessage();

        } catch (error) {
            this.log('❌ 安装过程中发生错误', 'error');
            this.log(error.message, 'error');
            process.exit(1);
        }
    }
}

// 如果直接运行此文件
if (require.main === module) {
    const installer = new DatabaseInstaller();
    installer.run();
}

module.exports = DatabaseInstaller;
